﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for CustomerContact Controller
    /// </summary>
    public class NPSCustomerContactController : ControllerBase
    {

        public List<Entities.NPSCustomerContact> NPSCustomerContactList
        {
            get
            {
                return getNPSCustomerContactList(false);
            }
        }

        public List<Entities.NPSCustomerContact> NPSCustomerContactListwithInfo
        {
            get
            {
                return getNPSCustomerContactList(true);
            }
        }

        public List<Entities.NPSCustomerContact> getNPSCustomerContactList(string name, string company)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"
                    select  
                        cpc.company_pic_id,
                        cpc.pic_name,
                        cpc.GENDER,
                        cpc.p_pic_position_id,
                        pp.code as position_name,
                        NVL (cpi1.INFO_DESC, '') email,
                        NVL (cpi2.INFO_DESC, '') phone,
                        NVL (cpi3.INFO_DESC, '') hp,
                        cpc.company_id,
                        cp.company_no,
                        cp.company_name,
                        cp.full_name,
                        cs.P_company_segment_id,
                        cs.code as company_segment,
                        cc.P_customer_class_id,
                        cc.code as company_class
                    from 
                        sni.company_pic cpc
                    left join
                        sni.company cp
                        on cpc.company_id=cp.company_id
                    LEFT JOIN
                        sni.p_pic_position pp
                        ON cpc.p_pic_position_id = pp.p_pic_position_id
                    left join
                        sni.company cp
                        on cpc.company_id=cp.company_id
                    left join
                        sni.p_customer_class cc
                        on cp.p_customer_class_id=cc.P_customer_class_id
                    left join
                        sni.p_company_segment cs
                        on cp.p_company_segment_id=cs.p_company_segment_id
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi1
                         ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
                         AND cpi1.P_INFO_TYPE_ID = 7
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi2
                         ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
                         AND cpi2.P_INFO_TYPE_ID = 5                    
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi3
                         ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
                         AND cpi3.P_INFO_TYPE_ID = 23            
                    where
                        NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and upper(cpc.pic_name) 
                        like upper('%" + name + "%') and upper(cp.company_name) like upper('%" + company + 
                        "%') and rownum<=40"
                    ;

            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSCustomerContact> CustomerContactList = new List<Entities.NPSCustomerContact>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSCustomerContact ciCustomerContact = new Entities.NPSCustomerContact();
                ciCustomerContact.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomerContact.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomerContact.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomerContact.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomerContact.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomerContact.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomerContact.sxCompanyClass = row["company_class"].ToString();
                ciCustomerContact.sxCompanyFullName = row["full_name"].ToString();
                ciCustomerContact.sxCompanyName = row["company_name"].ToString();
                ciCustomerContact.sxCompanySegment = row["company_segment"].ToString();
                ciCustomerContact.sxEmail = row["email"].ToString();
                ciCustomerContact.sxGender = row["GENDER"].ToString();
                ciCustomerContact.sxHP = row["hp"].ToString();
                ciCustomerContact.sxPhone = row["phone"].ToString();
                ciCustomerContact.sxPICName = row["pic_name"].ToString();
                ciCustomerContact.sxPosition = row["position_name"].ToString();
                ciCustomerContact.isNPSInfoIncluded = false;
                CustomerContactList.Add(ciCustomerContact);
            }
            return CustomerContactList;
        }

        protected List<Entities.NPSCustomerContact> getNPSCustomerContactList(bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select  
    cpc.company_pic_id,
    cpc.pic_name,
    cpc.GENDER,
    cpc.p_pic_position_id,
    pp.code as position_name,
    NVL (cpi1.INFO_DESC, '') email,
    NVL (cpi2.INFO_DESC, '') phone,
    NVL (cpi3.INFO_DESC, '') hp,
    cpc.company_id,
    cp.company_no,
    cp.company_name,
    cp.full_name,
    cs.P_company_segment_id,
    cs.code as company_segment,
    cc.P_customer_class_id,
    cc.code as company_class
    
from 
    sni.company_pic cpc
left join
    sni.company cp
    on cpc.company_id=cp.company_id
LEFT JOIN
    sni.p_pic_position pp
    ON cpc.p_pic_position_id = pp.p_pic_position_id
left join
    sni.p_customer_class cc
    on cp.p_customer_class_id=cc.P_customer_class_id
left join
    sni.p_company_segment cs
    on cp.p_company_segment_id=cs.p_company_segment_id
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi1
     ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
     AND cpi1.P_INFO_TYPE_ID = 7
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi2
     ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
     AND cpi2.P_INFO_TYPE_ID = 5                    
LEFT JOIN
     sni.COMPANY_PIC_INFO cpi3
     ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
     AND cpi3.P_INFO_TYPE_ID = 23            
where
    NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and rownum<=40";
            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSCustomerContact> CustomerContactList = new List<Entities.NPSCustomerContact>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSCustomerContact ciCustomerContact = new Entities.NPSCustomerContact();
                ciCustomerContact.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomerContact.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomerContact.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomerContact.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomerContact.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomerContact.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomerContact.sxCompanyClass = row["company_class"].ToString();
                ciCustomerContact.sxCompanyFullName = row["full_name"].ToString();
                ciCustomerContact.sxCompanyName = row["company_name"].ToString();
                ciCustomerContact.sxCompanySegment = row["company_segment"].ToString();
                ciCustomerContact.sxEmail = row["email"].ToString();
                ciCustomerContact.sxGender = row["GENDER"].ToString();
                ciCustomerContact.sxHP = row["hp"].ToString();
                ciCustomerContact.sxPhone = row["phone"].ToString();
                ciCustomerContact.sxPICName = row["pic_name"].ToString();
                ciCustomerContact.sxPosition = row["position_name"].ToString();
                ciCustomerContact.isNPSInfoIncluded = false;
                CustomerContactList.Add(ciCustomerContact);
            }
            return CustomerContactList;
        }

        public Entities.NPSCustomerContact getNPSCustomerContact(int CustomerContactID, bool NPSInfo)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"
                    select  
                        cpc.company_pic_id,
                        cpc.pic_name,
                        cpc.GENDER,
                        cpc.p_pic_position_id,
                        pp.code as position_name,
                        NVL (cpi1.INFO_DESC, '') email,
                        NVL (cpi2.INFO_DESC, '') phone,
                        NVL (cpi3.INFO_DESC, '') hp,
                        cpc.company_id,
                        cp.company_no,
                        cp.company_name,
                        cp.full_name,
                        cs.P_company_segment_id,
                        cs.code as company_segment,
                        cc.P_customer_class_id,
                        cc.code as company_class
                    from 
                        sni.company_pic cpc
                    left join
                        sni.company cp
                        on cpc.company_id=cp.company_id
                    LEFT JOIN
                        sni.p_pic_position pp
                        ON cpc.p_pic_position_id = pp.p_pic_position_id
                    left join
                        sni.p_customer_class cc
                        on cp.p_customer_class_id=cc.P_customer_class_id
                    left join
                        sni.p_company_segment cs
                        on cp.p_company_segment_id=cs.p_company_segment_id
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi1
                         ON cpc.COMPANY_PIC_ID = cpi1.COMPANY_PIC_ID
                         AND cpi1.P_INFO_TYPE_ID = 7
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi2
                         ON cpc.COMPANY_PIC_ID = cpi2.COMPANY_PIC_ID
                         AND cpi2.P_INFO_TYPE_ID = 5                    
                    LEFT JOIN
                         sni.COMPANY_PIC_INFO cpi3
                         ON cpc.COMPANY_PIC_ID = cpi3.COMPANY_PIC_ID
                         AND cpi3.P_INFO_TYPE_ID = 23 
                    where
                        NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and cpc.company_pic_id=:prmID"
                ;

            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = CustomerContactID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
           
            DataRow row = dt.Rows[0];
            dt.Dispose();
            Entities.NPSCustomerContact ciCustomerContact = new Entities.NPSCustomerContact();
            try
            {
                ciCustomerContact.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                ciCustomerContact.iCompanyClassID = Convert.ToInt32(row["P_customer_class_id"].ToString());
                ciCustomerContact.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                ciCustomerContact.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                ciCustomerContact.iCompanySegmentID = Convert.ToInt32(row["P_company_segment_id"].ToString());
                ciCustomerContact.iPositionID = Convert.ToInt32(row["p_pic_position_id"].ToString());
                ciCustomerContact.sxCompanyClass = row["company_class"].ToString();
                ciCustomerContact.sxCompanyFullName = row["full_name"].ToString();
                ciCustomerContact.sxCompanyName = row["company_name"].ToString();
                ciCustomerContact.sxCompanySegment = row["company_segment"].ToString();
                ciCustomerContact.sxEmail = row["email"].ToString();
                ciCustomerContact.sxGender = row["GENDER"].ToString();
                ciCustomerContact.sxHP = row["hp"].ToString();
                ciCustomerContact.sxPhone = row["phone"].ToString();
                ciCustomerContact.sxPICName = row["pic_name"].ToString();
                ciCustomerContact.sxPosition = row["position_name"].ToString();
                if (NPSInfo)
                {
                    try
                    {
                        ciCustomerContact.isNPSInfoIncluded = true;
                        Controller.NPSSurveyController sc = new NPSSurveyController();
                        ciCustomerContact.oNPSInfo = sc.getNPSSurveyList(ciCustomerContact.iID);
                    }
                    catch { ciCustomerContact.isNPSInfoIncluded = false; }
                }
                else
                {
                    ciCustomerContact.isNPSInfoIncluded = false;
                }
            }
            catch (Exception p) { errLog = p.ToString(); }


            return ciCustomerContact;
        }

        

        public List<Entities.NPSCustomerContact> getCustomerBatch(int Year)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select   distinct
    cpc.company_pic_id,
    cpc.pic_name,
    cpc.p_pic_position_id,
    typ.title as npstype,
    cpc.company_id,
    cp.company_no,
    cp.company_name,
    cp.full_name,
    srv.score
from 
    sni.company_pic cpc
left join
    sni.company cp
    on cpc.company_id=cp.company_id
LEFT JOIN
     icp.NPS_SURVEY srv
     ON cpc.COMPANY_PIC_ID = p_Contact_Person_ID
     LEFT JOIN
     icp.P_NPS_TYPE typ
     ON srv.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
where
    NVL (cpc.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            DataTable dt = dbl.GetDataTable(sxQuery,lsParams.ToArray());
            List<Entities.NPSCustomerContact> ciCustomers = new List<Entities.NPSCustomerContact>();
            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSCustomerContact ciCustomer = new Entities.NPSCustomerContact();
                    ciCustomer.iID = Convert.ToInt32(row["company_pic_id"].ToString());
                    ciCustomer.iCompanyID = Convert.ToInt32(row["company_id"].ToString());
                    ciCustomer.iCompanyNo = Convert.ToInt32(row["company_no"].ToString());
                    ciCustomer.sxCompanyFullName = row["full_name"].ToString();
                    ciCustomer.sxCompanyName = row["company_name"].ToString();
                    ciCustomer.sxPICName = row["pic_name"].ToString();
                    ciCustomer.hasNPS=true;
                    ciCustomer.iNPSScore=Convert.ToInt32(row["score"].ToString());
                    ciCustomer.sxNPSType= row["npstype"].ToString();
                    ciCustomers.Add(ciCustomer);
                }

            }
            catch (Exception p) { errLog = p.ToString(); }
            return ciCustomers;
        }

        public DataTable getSummaryTotal(int Year)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select typ.title,count(typ.title) as total
                                from nps_survey srv
                                join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
                                where
                                NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID 
                                group by typ.Title";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            return dbl.GetDataTable(sxQuery, lsParams.ToArray());
        }

        public DataTable getSummaryCompany(int Year, int CompanyID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select typ.title,count(typ.title) as total
                               from nps_survey srv
                               join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
                               join sni.company_pic cpc on srv.p_contact_person_id=cpc.company_pic_id
                               where cpc.company_ID=:prmCompany and NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID
                               group by typ.Title";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmCompany";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = CompanyID;
            return dbl.GetDataTable(sxQuery, lsParams.ToArray());
        }

        public DataTable getSummarySegment(int Year, int SegmentID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select typ.title,count(typ.title) as total
                               from nps_survey srv
                               join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
                               join sni.company_pic cpc on srv.p_contact_person_id=cpc.company_pic_id
                               join sni.company cp on cpc.company_id=cp.company_id
                               where cp.p_company_segment_ID=:prmSegmentID
                               and NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID
                               group by typ.Title";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmSegmentID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = SegmentID;
            return dbl.GetDataTable(sxQuery, lsParams.ToArray());
        }

        public DataTable getSummaryClass(int Year, int ClassID)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select typ.title,count(typ.title) as total
                               from nps_survey srv
                               join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
                               join sni.company_pic cpc on srv.p_contact_person_id=cpc.company_pic_id
                               join sni.company cp on cpc.company_id=cp.company_id
                               where cp.p_customer_class_id=:prmClassID
                               and NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID
                               group by typ.Title";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmClassID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ClassID;
            return dbl.GetDataTable(sxQuery, lsParams.ToArray());
        }

        public DataTable getCompanySummary(int Year)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select distinct cp.company_name,cp.company_id
from nps_survey srv
join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
join sni.company_pic cpc on srv.p_contact_person_id=cpc.company_pic_id
join sni.company cp on cpc.company_id=cp.company_id
where NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID
order by cp.company_name";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            return dbl.GetDataTable(sxQuery,lsParams.ToArray());
        }
        
        public DataTable getClassSummary()
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select code,p_customer_class_id from sni.p_customer_class where p_customer_status_id=1 order by p_customer_class_id";
            return dbl.GetDataTable(sxQuery);
        }

        public DataTable getSegmentSummary(int Year)
        {
            DBLib dbl = new DBLib();
            String sxQuery = @"select distinct cs.code,cp.p_company_segment_ID
from nps_survey srv
join p_nps_type typ on SRV.P_NPS_TYPE_ID = typ.P_NPS_TYPE_ID
join sni.company_pic cpc on srv.p_contact_person_id=cpc.company_pic_id
join sni.company cp on cpc.company_id=cp.company_id
join sni.p_company_segment cs on cp.P_company_segment_id=cs.p_company_segment_id
where NVL (srv.VALID_TO, SYSDATE) >= SYSDATE and extract(year from srv.survey_date)=:prmID
order by cs.code";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = Year;
            return dbl.GetDataTable(sxQuery,lsParams.ToArray());
        }
        


       
    }
}